using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;

namespace OgilvyOne.SqlServerDAL
{
    public class Member : IMember
    {
        // Fields
        private const string PARM_EMAIL = "@Email";
        private const string PARM_ISDEL = "@IsDel";
        private const string PARM_LOGIN_NAME = "@LoginName";
        private const string PARM_MEMBER_ID = "@MemberId";
        private const string PARM_PASSWORD = "@Password";
        private const string PARM_TELEPHONE = "@Telephone";
        private const string PARM_USER_NAME = "@UserName";
        private const string SQL_INSERT_MEMBER = "INSERT INTO member_tbl VALUES(@UserName, @LoginName, @Password, @Email,@Telephone,'0')";
        private const string SQL_SELECT_MEMBER = "SELECT Member_ID,User_Name,Login_Name,Password,Email,Telephone,IsDel FROM member_tbl  where Login_Name =@LoginName and.Password = @Password";
        private const string SQL_SELECT_MEMBER_INFO = "SELECT Member_ID,User_Name,Login_Name,Password,Email,Telephone,IsDel FROM member_tbl where isdel=@IsDel and member_id=@MemberId";
        private const string SQL_SELECT_MEMBER_LIST = "SELECT Member_ID,User_Name,Login_Name,Password,Email,Telephone,IsDel FROM member_tbl where isdel=@IsDel";
        private const string SQL_UPDATE_MEMBER = "UPDATE member_tbl SET User_Name = @UserName, Login_Name = @LoginName, Password = @Password, Email = @Email,Telephone = @Telephone WHERE Member_ID = @MemberId";

        // Methods
        public MemberInfo CheckLogin(string login_name, string password)
        {
            SqlParameter[] checkLoginParameters = GetCheckLoginParameters();
            checkLoginParameters[0].Value = login_name;
            checkLoginParameters[1].Value = password;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT Member_ID,User_Name,Login_Name,Password,Email,Telephone,IsDel FROM member_tbl  where Login_Name =@LoginName and.Password = @Password", checkLoginParameters))
            {
                if (reader.Read())
                {
                    return new MemberInfo(reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6));
                }
                return null;
            }
        }

        private static SqlParameter[] GetCheckLoginParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("SELECT Member_ID,User_Name,Login_Name,Password,Email,Telephone,IsDel FROM member_tbl  where Login_Name =@LoginName and.Password = @Password");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@LoginName", SqlDbType.VarChar, 80), new SqlParameter("@Password", SqlDbType.VarChar, 80) };
                SQLHelper.CacheParameters("SELECT Member_ID,User_Name,Login_Name,Password,Email,Telephone,IsDel FROM member_tbl  where Login_Name =@LoginName and.Password = @Password", cachedParameters);
            }
            return cachedParameters;
        }

        public IList getMemberList(string isdel)
        {
            IList list = new ArrayList();
            SqlParameter parameter = new SqlParameter("@IsDel", SqlDbType.Char, 1);
            parameter.Value = isdel;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT Member_ID,User_Name,Login_Name,Password,Email,Telephone,IsDel FROM member_tbl where isdel=@IsDel", new SqlParameter[] { parameter }))
            {
                while (reader.Read())
                {
                    MemberInfo info = new MemberInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5));
                    list.Add(info);
                }
            }
            return list;
        }

        public IList getMemberList(string isdel, int memberid)
        {
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MemberId", SqlDbType.Int, 4, "member_id"), new SqlParameter("@IsDel", SqlDbType.Char, 1, "isdel") };
            cmdParms[0].Value = memberid;
            cmdParms[1].Value = isdel;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT Member_ID,User_Name,Login_Name,Password,Email,Telephone,IsDel FROM member_tbl where isdel=@IsDel and member_id=@MemberId", cmdParms))
            {
                while (reader.Read())
                {
                    MemberInfo info = new MemberInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5));
                    list.Add(info);
                }
            }
            return list;
        }

        private static SqlParameter[] GetMemberParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("INSERT INTO member_tbl VALUES(@UserName, @LoginName, @Password, @Email,@Telephone,'0')");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@UserName", SqlDbType.VarChar, 80), new SqlParameter("@LoginName", SqlDbType.VarChar, 80), new SqlParameter("@Password", SqlDbType.VarChar, 80), new SqlParameter("@Email", SqlDbType.VarChar, 80), new SqlParameter("@Telephone", SqlDbType.VarChar, 80) };
                SQLHelper.CacheParameters("INSERT INTO member_tbl VALUES(@UserName, @LoginName, @Password, @Email,@Telephone,'0')", cachedParameters);
            }
            return cachedParameters;
        }

        private static SqlParameter[] GetMemberParametersID()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("INSERT INTO member_tbl VALUES(@UserName, @LoginName, @Password, @Email,@Telephone,'0')");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@MemberId", SqlDbType.Int, 4), new SqlParameter("@UserName", SqlDbType.VarChar, 80), new SqlParameter("@LoginName", SqlDbType.VarChar, 80), new SqlParameter("@Password", SqlDbType.VarChar, 80), new SqlParameter("@Email", SqlDbType.VarChar, 80), new SqlParameter("@Telephone", SqlDbType.VarChar, 80) };
                SQLHelper.CacheParameters("INSERT INTO member_tbl VALUES(@UserName, @LoginName, @Password, @Email,@Telephone,'0')", cachedParameters);
            }
            return cachedParameters;
        }

        public void Insert(MemberInfo acc)
        {
            SqlParameter[] memberParameters = GetMemberParameters();
            this.SetMemberParameters(memberParameters, acc);
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "INSERT INTO member_tbl VALUES(@UserName, @LoginName, @Password, @Email,@Telephone,'0')", memberParameters);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        private void SetMemberParameters(SqlParameter[] parms, MemberInfo acc)
        {
            parms[0].Value = acc.UserName;
            parms[1].Value = acc.LoginName;
            parms[2].Value = acc.Password;
            parms[3].Value = acc.Email;
            parms[4].Value = acc.Telephone;
        }

        private void SetMemberParametersID(SqlParameter[] parms, MemberInfo acc)
        {
            parms[0].Value = acc.MemberID;
            parms[1].Value = acc.UserName;
            parms[2].Value = acc.LoginName;
            parms[3].Value = acc.Password;
            parms[4].Value = acc.Email;
            parms[5].Value = acc.Telephone;
        }

        public void Update(MemberInfo myMember)
        {
            SqlParameter[] memberParametersID = GetMemberParametersID();
            this.SetMemberParametersID(memberParametersID, myMember);
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "UPDATE member_tbl SET User_Name = @UserName, Login_Name = @LoginName, Password = @Password, Email = @Email,Telephone = @Telephone WHERE Member_ID = @MemberId", memberParametersID);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}
